﻿using DataSyncCommon;
using MySql.Data.MySqlClient;
using NodaTime;
using SqlSugar;
using System;
using System.Data;
using MySqlDataAdapter = MySql.Data.MySqlClient.MySqlDataAdapter;

namespace DataSyncCom
{
    /// <summary>
    /// MySql数据库操作
    /// </summary>
    public class DbHelperMySql
    {

        /// <summary>
        /// 执行命令，（即不返回结果集）
        /// </summary>
        /// <param name="sql">sql节点名</param>
        /// <returns>返回影响行数</returns>
        public static int ExecuteNoQuery(string sql)
        {
            int val = 0;
            MySqlConnection conn = null;
            try
            {
                conn = ConnOpen();
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                val = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                //LogHelper.WriteLog(LogType.Error, "执行ExecuteNoQuery异常：" + e.Message, sql);
                throw e;
            }
            finally
            {
                ConnClose(conn);
            }
            return val;
        }

        /// <summary>
        /// 执行命令，返回一个DataSet，对数据库中指定的连接字符串
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="ds">输出参数 DataSet</param>
        /// <returns>执行成功返回 true；反之，返回false。</returns>
        public static bool ExecuteQuery(string sql, out DataSet ds)
        {
            ds = null;
            MySqlConnection conn = null;
            try
            {
                conn = ConnOpen();
                MySqlDataAdapter mysqlDa = new MySql.Data.MySqlClient.MySqlDataAdapter(sql, conn);
                ds = new DataSet();
                mysqlDa.Fill(ds);
                return true;
            }
            catch (Exception e)
            {
                //LogHelper.WriteLog(LogType.Error, "执行ExecuteQuery异常：" + e.Message, sql);
                throw e;
            }
            finally
            {
                ConnClose(conn);
            }
        }

        /// <summary>
        /// 执行带指定数据库连接的查询
        /// </summary>
        /// <param name="connstr"></param>
        /// <param name="sql"></param>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static bool ExecuteQueryByConnstr(string connstr, string sql, out DataSet ds)
        {
            ds = null;
            MySqlConnection conn = null;
            try
            {
                conn = new MySqlConnection(connstr);
                conn.Open();
                MySqlDataAdapter mysqlDa = new MySqlDataAdapter(sql, conn);
                ds = new DataSet();
                mysqlDa.Fill(ds);
                return true;
            }
            catch (Exception e)
            {
                //LogHelper.WriteLog(LogType.Error, "执行ExecuteQueryByConnstr异常：" + e.Message, sql);
                throw e;
            }
            finally
            {
                ConnClose(conn);
            }
        }

        public static MySqlConnection ConnOpen()
        {
            try
            {
                var conn = new MySqlConnection(SysConfig.ConnectionStr);
                conn.Open();
                return conn;
            }
            catch (Exception e)
            {
                //LogHelper.WriteLog(LogType.Error, "数据库连接失败：" + e.Message);
                throw new Exception("数据库连接失败：" + e.Message);
            }
        }

        public static void ConnClose(MySqlConnection conn)
        {
            try
            {
                if (null != conn)
                {
                    if (!conn.State.Equals(ConnectionState.Closed))
                    {
                        conn.Close();
                        conn.Dispose();
                        conn = null;
                    }
                }
            }
            catch (MySqlException e)
            {
                //LogHelper.WriteLog(LogType.Error, "数据库断开失败：" + e.Message);
                throw new Exception("数据库断开失败：" + e.Message);
            }
        }

        #region SqlSugar
        public static SqlSugarClient GetSqlSugarDb()
        {
            SqlSugarClient db = new SqlSugarClient(
                new ConnectionConfig()
                {
                    ConnectionString = SysConfig.ConnectionStr==null? "server='49.4.70.226';port=3306;uid='root';pwd='aaabbb123';charset='utf8';pooling=true":SysConfig.ConnectionStr,
                    DbType = SqlSugar.DbType.MySql,//设置数据库类型
                    IsAutoCloseConnection = true,//自动释放数据务，如果存在事务，在事务结束后释放
                    InitKeyType = InitKeyType.Attribute //从实体特性中读取主键自增列信息
                }); 
            return db;
        }
        #endregion
    }

    public class DateTimeExt
    {
        public static DateTime GetCstDateTime()
        {
            Instant now = SystemClock.Instance.GetCurrentInstant();
            var shanghaiZone = DateTimeZoneProviders.Tzdb["Asia/Shanghai"];
            return now.InZone(shanghaiZone).ToDateTimeUnspecified();
        }
    }
    /// <summary>
    /// DateTime.Now.ToCstTime();
    /// </summary>
    public static class DateTimeExtentions
    {
        public static DateTime ToCstTime(this DateTime time)
        {
            return DateTimeExt.GetCstDateTime();
        }
    }
}

